package by.grsu.bso.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import by.grsu.bso.model.ShoppingCartItem;
import by.grsu.bso.persistence.base.IShoppingCartItemDAO;
import by.grsu.bso.persistence.util.DaoManager;

public class ShoppingCartItemDAO extends DaoManager implements IShoppingCartItemDAO {

	private static final String SAVE_ITEM_QUERY = "INSERT INTO shopdb.Shoppingcartitem(UserID, ProductID, Quantity) VALUES (?, ?, ?);";
	private static final String ITEM_IS_SAME_QUERY = "SELECT UserID FROM shopdb.shoppingcartitem WHERE (UserID = ?) AND (ProductID = ?);";
	private static final String GET_LIST_ITEMS_BY_USER_ID_QUERY = "SELECT ShoppingCartItemID, UserID, ProductID, Quantity FROM shopdb.ShoppingCartItem WHERE UserID = ?;";
	private static final String UPDATE_ITEMS_BY_ID_QUERY = "UPDATE shopdb.ShoppingCartItem SET UserID = ? WHERE UserID = ?;";
	private static final String DELETE_ITEM_BY_CART_ID_QUERY = "DELETE FROM shopdb.ShoppingCartItem WHERE ShoppingCartItemID = ?;";
	private static final String UPDATE_QUANTITY_QUERY = "UPDATE shopdb.ShoppingCartItem SET Quantity = ? WHERE ShoppingCartItemID = ?;";
	private static final String GET_CART_ITEM_BY_CART_ID_QUERY = "SELECT ShoppingCartItemID, UserID, ProductID, Quantity FROM shopdb.ShoppingCartItem WHERE ShoppingCartItemID = ?;";
	
	@Override
	public List<ShoppingCartItem> get() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ShoppingCartItem findById(int cartId) throws SQLException {
		
		Connection conn = getConnection();
		
		ShoppingCartItem cartItem = null;
		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.GET_CART_ITEM_BY_CART_ID_QUERY);
		stmt.setInt(1, cartId);
		ResultSet rs = stmt.executeQuery();
		
		if (rs.next()) {
			cartItem =  new ShoppingCartItem(
							rs.getInt("ShoppingCartItemID"),
							rs.getInt("UserID"),
							rs.getInt("ProductID"),
							rs.getInt("Quantity") );
		}
		
		closeConnection(conn, rs, stmt);
		
		return cartItem;
	}

	@Override
	public List<ShoppingCartItem> findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<ShoppingCartItem> findByExample(ShoppingCartItem exampleInstance) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int countAll() {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int countByExample(ShoppingCartItem exampleInstance) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public ShoppingCartItem save(ShoppingCartItem entity) {
				
		return null;
	}

	@Override
	public void delete(int cartId) throws SQLException {
		
		Connection conn = getConnection();
		
		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.DELETE_ITEM_BY_CART_ID_QUERY);
		stmt.setInt(1, cartId);
		stmt.executeUpdate();
		
		closeConnection(conn, null, stmt);	
		
	}

	@Override
	public ShoppingCartItem create() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void saveItem(ShoppingCartItem item) throws SQLException {
		Connection conn = getConnection();

		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.SAVE_ITEM_QUERY);
		stmt.setInt(1, item.getUserId());
		stmt.setInt(2, item.getProductId());
		stmt.setInt(3, item.getQuantity());
		stmt.executeUpdate();
		
		closeConnection(conn, null, stmt);		
	}

	@Override
	public boolean isSame(int userId, int productId) throws SQLException {
		
		Connection conn = getConnection();
		
		boolean b = false;
		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.ITEM_IS_SAME_QUERY);
		stmt.setInt(1, userId);
		stmt.setInt(2, productId);
		ResultSet rs = stmt.executeQuery();
		
		if (rs.next()) {
			b = true;
		}
		
		closeConnection(conn, rs, stmt);
		
		return b;

	}

	@Override
	public List<ShoppingCartItem> findAllByUserId(int id) throws SQLException {
		Connection conn = getConnection();
		
		List<ShoppingCartItem> itemList = new ArrayList<ShoppingCartItem>();
		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.GET_LIST_ITEMS_BY_USER_ID_QUERY);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		
		while (rs.next()) {
			itemList.add( new ShoppingCartItem(
							rs.getInt("ShoppingCartItemID"),
							rs.getInt("UserID"),
							rs.getInt("ProductID"),
							rs.getInt("Quantity")) );
		}
		
		closeConnection(conn, rs, stmt);
		
		return itemList;
	}

	@Override
	public void update(Integer userOldId, Integer userNewId) throws SQLException {
		
		Connection conn = getConnection();
		
		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.UPDATE_ITEMS_BY_ID_QUERY);
		stmt.setInt(1, userNewId);
		stmt.setInt(2, userOldId);
		stmt.executeUpdate();
		
		closeConnection(conn, null, stmt);	
	}

	@Override
	public void updateQuantity(Integer itemId, Integer newQuantity)
			throws SQLException {
		Connection conn = getConnection();
		
		PreparedStatement stmt = conn.prepareStatement(ShoppingCartItemDAO.UPDATE_QUANTITY_QUERY);
		stmt.setInt(1, newQuantity);
		stmt.setInt(2, itemId);
		stmt.executeUpdate();
		
		closeConnection(conn, null, stmt);	
		
	}

	

}
